<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
<meta name="viewport"
      content="width=device-width, initial-scale=1.0, maximum-scale=1.0, minimum-scale=1.0">
<meta http-equiv="X-UA-Compatible" content="ie=edge">

    <meta name="author" content="WD">





<title>Mysql学习笔记(四)——多表查询 | WD&#39;s blog</title>



    <link rel="icon" href="/favicon1.ico">




    <!-- stylesheets list from _config.yml -->
    
    <link rel="stylesheet" href="/css/style.css">
    



    <!-- scripts list from _config.yml -->
    
    <script src="/js/script.js"></script>
    
    <script src="/js/tocbot.min.js"></script>
    
    <script src="/js/snow.js"></script>
    



    
    
        
    


<meta name="generator" content="Hexo 5.4.0"></head>
<body>
    <div class="wrapper">
        <header>
    <nav class="navbar">
        <div class="container">
            <div class="navbar-header header-logo"><a href="/">WD&#39;s Blog</a></div>
            <div class="menu navbar-right">
                
                    <a class="menu-item" href="/archives">Posts</a>
                
                    <a class="menu-item" href="/category">Categories</a>
                
                    <a class="menu-item" href="/tag">Tags</a>
                
                    <a class="menu-item" href="/about">About</a>
                
                <input id="switch_default" type="checkbox" class="switch_default">
                <label for="switch_default" class="toggleBtn"></label>
            </div>
        </div>
    </nav>

    
    <nav class="navbar-mobile" id="nav-mobile">
        <div class="container">
            <div class="navbar-header">
                <div>
                    <a href="/">WD&#39;s Blog</a><a id="mobile-toggle-theme">·&nbsp;Light</a>
                </div>
                <div class="menu-toggle" onclick="mobileBtn()">&#9776; Menu</div>
            </div>
            <div class="menu" id="mobile-menu">
                
                    <a class="menu-item" href="/archives">Posts</a>
                
                    <a class="menu-item" href="/category">Categories</a>
                
                    <a class="menu-item" href="/tag">Tags</a>
                
                    <a class="menu-item" href="/about">About</a>
                
            </div>
        </div>
    </nav>

</header>
<script>
    var mobileBtn = function f() {
        var toggleMenu = document.getElementsByClassName("menu-toggle")[0];
        var mobileMenu = document.getElementById("mobile-menu");
        if(toggleMenu.classList.contains("active")){
           toggleMenu.classList.remove("active")
            mobileMenu.classList.remove("active")
        }else{
            toggleMenu.classList.add("active")
            mobileMenu.classList.add("active")
        }
    }
</script>
        <div class="main">
            <div class="container">
    
    
        <div class="post-toc">
    <div class="tocbot-list">
    </div>
    <div class="tocbot-list-menu">
        <a class="tocbot-toc-expand" onclick="expand_toc()">Expand all</a>
        <a onclick="go_top()">Back to top</a>
        <a onclick="go_bottom()">Go to bottom</a>
    </div>
</div>

<script>
    document.ready(
        function () {
            tocbot.init({
                tocSelector: '.tocbot-list',
                contentSelector: '.post-content',
                headingSelector: 'h1, h2, h3, h4, h5',
                collapseDepth: 1,
                orderedList: false,
                scrollSmooth: true,
            })
        }
    )

    function expand_toc() {
        var b = document.querySelector(".tocbot-toc-expand");
        tocbot.init({
            tocSelector: '.tocbot-list',
            contentSelector: '.post-content',
            headingSelector: 'h1, h2, h3, h4, h5',
            collapseDepth: 6,
            orderedList: false,
            scrollSmooth: true,
        });
        b.setAttribute("onclick", "collapse_toc()");
        b.innerHTML = "Collapse all"
    }

    function collapse_toc() {
        var b = document.querySelector(".tocbot-toc-expand");
        tocbot.init({
            tocSelector: '.tocbot-list',
            contentSelector: '.post-content',
            headingSelector: 'h1, h2, h3, h4, h5',
            collapseDepth: 1,
            orderedList: false,
            scrollSmooth: true,
        });
        b.setAttribute("onclick", "expand_toc()");
        b.innerHTML = "Expand all"
    }

    function go_top() {
        window.scrollTo(0, 0);
    }

    function go_bottom() {
        window.scrollTo(0, document.body.scrollHeight);
    }

</script>
    

    
    <article class="post-wrap">
        <header class="post-header">
            <h1 class="post-title">Mysql学习笔记(四)——多表查询</h1>
            
                <div class="post-meta">
                    
                        Author: <a itemprop="author" rel="author" href="/about/">WD</a>
                     &nbsp;

                    
                        <span class="post-time">
                        Date: <a href="#">June 17, 2019&nbsp;&nbsp;15:33:52</a>
                        </span>
                     &nbsp;
                    
                        <span class="post-category">
                    Category:
                            
                                <a href="/categories/Mysql/">Mysql</a>
                            
                        </span>
    <script async src="https://busuanzi.ibruce.info/busuanzi/2.3/busuanzi.pure.mini.js"></script>
    <br>
    <span id="busuanzi_container_site_pv">总阅读量:<a href="#"><span id="busuanzi_value_page_pv"></span></a>次</span>&nbsp;
    <span class="post-count">文章字数:<a href="#">5.4k</span></a>&nbsp;
     <span class="post-count">阅读时长:<a href="#">24</span>min</a>
                    
                </div>
            
        </header>

        <div class="post-content">
            <h2 id="1-概述"><a href="#1-概述" class="headerlink" title="1.概述"></a>1.概述</h2><ul>
<li>连接查询：同时涉及多个表的查询<br>连接条件或连接谓词：用来连接两个表的条件<pre><code> 一般格式：
</code></pre>[&lt;表名1&gt;.]&lt;列名1&gt;  &lt;比较运算符&gt;  [&lt;表名2&gt;.]&lt;列名2&gt;<br>[&lt;表名1&gt;.]&lt;列名1&gt; BETWEEN [&lt;表名2&gt;.]&lt;列名2&gt; AND [&lt;表名2&gt;.]&lt;列名3&gt;<br>连接字段：连接谓词中的列名称<br>连接条件中的各连接字段类型必须是可比的，但名字不必是相同的</li>
</ul>
<h2 id="2-等值和非等值连接"><a href="#2-等值和非等值连接" class="headerlink" title="2.等值和非等值连接"></a>2.等值和非等值连接</h2><ul>
<li><p>等值连接：连接运算符为=<br>[例33]  查询每个学生及其选修课程的情况<br>SELECT  Student.*，SC.*<br>FROM     Student，SC<br>WHERE  Student.Sno = SC.Sno；</p>
<figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br></pre></td><td class="code"><pre><span class="line"><span class="meta">mysql&gt;</span><span class="bash"> select student.*,sc.* from student,sc <span class="built_in">where</span> Student.Sno = sc.Sno;</span></span><br><span class="line">+-------+-----------+------+------+-------+-------+-----+-------+</span><br><span class="line">| Sno   | Sname     | Ssex | Sage | Sdept | Sno   | Cno | Grade |</span><br><span class="line">+-------+-----------+------+------+-------+-------+-----+-------+</span><br><span class="line">| 95001 | 张三      | 男   |   21 | CS    | 95001 | 1   |    93 |</span><br><span class="line">| 95001 | 张三      | 男   |   21 | CS    | 95001 | 2   |    95 |</span><br><span class="line">| 95001 | 张三      | 男   |   21 | CS    | 95001 | 3   |    91 |</span><br><span class="line">| 95001 | 张三      | 男   |   21 | CS    | 95001 | 4   |    98 |</span><br><span class="line">| 95002 | 李四      | 男   |   23 | IS    | 95002 | 2   |    90 |</span><br><span class="line">| 95002 | 李四      | 男   |   23 | IS    | 95002 | 3   |    80 |</span><br><span class="line">| 95003 | 王五      | 男   |   19 | MA    | 95003 | 2   |  NULL |</span><br><span class="line">| 95004 | 马六      | 女   |   20 | CS    | 95004 | 3   |  NULL |</span><br><span class="line">| 95004 | 马六      | 女   |   20 | CS    | 95004 | 4   |    88 |</span><br><span class="line">| 95005 | 苏三      | 女   |   20 | IS    | 95005 | 1   |    90 |</span><br><span class="line">| 95005 | 苏三      | 女   |   20 | IS    | 95005 | 2   |    98 |</span><br><span class="line">| 95005 | 苏三      | 女   |   20 | IS    | 95005 | 3   |    90 |</span><br><span class="line">| 95005 | 苏三      | 女   |   20 | IS    | 95005 | 4   |    89 |</span><br><span class="line">| 95011 | 张成民    | 男   |   19 | CS    | 95011 | 1   |  NULL |</span><br><span class="line">+-------+-----------+------+------+-------+-------+-----+-------+</span><br><span class="line">14 rows in set (0.01 sec)</span><br></pre></td></tr></table></figure>
<blockquote>
<p>此时发现表中有两竖列的值是相同的，所以是冗余的。</p>
</blockquote>
</li>
<li><p>自然连接：</p>
<p>[例34]     对上例用自然连接完成。<br> SELECT  Student.Sno，Sname，Ssex，Sage，Sdept，Cno，Grade<br> FROM     Student，SC<br> WHERE  Student.Sno = SC.Sno；</p>
<figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br></pre></td><td class="code"><pre><span class="line"><span class="meta">mysql&gt;</span><span class="bash"> select student.Sno,Sname,Ssex,Sage,Sdept,cno,Grade from student,sc <span class="built_in">where</span> Student.Sno=sc.sno;</span></span><br><span class="line">+-------+-----------+------+------+-------+-----+-------+</span><br><span class="line">| Sno   | Sname     | Ssex | Sage | Sdept | cno | Grade |</span><br><span class="line">+-------+-----------+------+------+-------+-----+-------+</span><br><span class="line">| 95001 | 张三      | 男   |   21 | CS    | 1   |    93 |</span><br><span class="line">| 95001 | 张三      | 男   |   21 | CS    | 2   |    95 |</span><br><span class="line">| 95001 | 张三      | 男   |   21 | CS    | 3   |    91 |</span><br><span class="line">| 95001 | 张三      | 男   |   21 | CS    | 4   |    98 |</span><br><span class="line">| 95002 | 李四      | 男   |   23 | IS    | 2   |    90 |</span><br><span class="line">| 95002 | 李四      | 男   |   23 | IS    | 3   |    80 |</span><br><span class="line">| 95003 | 王五      | 男   |   19 | MA    | 2   |  NULL |</span><br><span class="line">| 95004 | 马六      | 女   |   20 | CS    | 3   |  NULL |</span><br><span class="line">| 95004 | 马六      | 女   |   20 | CS    | 4   |    88 |</span><br><span class="line">| 95005 | 苏三      | 女   |   20 | IS    | 1   |    90 |</span><br><span class="line">| 95005 | 苏三      | 女   |   20 | IS    | 2   |    98 |</span><br><span class="line">| 95005 | 苏三      | 女   |   20 | IS    | 3   |    90 |</span><br><span class="line">| 95005 | 苏三      | 女   |   20 | IS    | 4   |    89 |</span><br><span class="line">| 95011 | 张成民    | 男   |   19 | CS    | 1   |  NULL |</span><br><span class="line">+-------+-----------+------+------+-------+-----+-------+</span><br><span class="line">14 rows in set (0.00 sec)</span><br></pre></td></tr></table></figure>
<blockquote>
<p>这里一定要注意选择的时候要用Student.Sno，不能单独只写一个sno，因为连接的两个表都有sno，这时区分不开到底是哪个。</p>
</blockquote>
</li>
</ul>
<h2 id="3-自身连接"><a href="#3-自身连接" class="headerlink" title="3.自身连接"></a>3.自身连接</h2><ul>
<li><p>自身连接：一个表与其自己进行连接<br>需要给表起别名以示区别<br>由于所有属性名都是同名属性，因此必须使用别名前缀</p>
<p>[例35]查询每一门课的间接先修课（即先修课的先修课）</p>
<pre><code>SELECT  FIRST.Cno，SECOND.Cpno
 FROM  Course  FIRST，Course  SECOND
 WHERE FIRST.Cpno = SECOND.Cno；
</code></pre><figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line"><span class="meta">mysql&gt;</span><span class="bash"> select First.Cno,Second.Cpno from Course First,Course Second <span class="built_in">where</span> first.cpno = second.cno;</span></span><br><span class="line">+-----+------+</span><br><span class="line">| Cno | Cpno |</span><br><span class="line">+-----+------+</span><br><span class="line">| 2   | 4    |</span><br><span class="line">| 3   | NULL |</span><br><span class="line">+-----+------+</span><br><span class="line">2 rows in set (0.01 sec)</span><br></pre></td></tr></table></figure>
</li>
</ul>
<h2 id="4-外连接"><a href="#4-外连接" class="headerlink" title="4.外连接"></a>4.外连接</h2><ul>
<li><p>外连接与普通连接的区别<br>普通连接操作只输出满足连接条件的元组<br>外连接操作以指定表为连接主体，将主体表中不满足连接条件的元组一并输出<br>[例 36] 改写[例35]<br>SELECT FIRST.Cno, SECOND.Cpno FROM Course FIRST LEFT JOIN Course SECOND ON (FIRST.Cpno=SECOND.Cno);    </p>
<figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line"><span class="meta">mysql&gt;</span><span class="bash"> select First.Cno,Second.Cpno from Course First LEFT JOIN Course Second ON(first.cpno = second.cno);</span></span><br><span class="line">+-----+------+</span><br><span class="line">| Cno | Cpno |</span><br><span class="line">+-----+------+</span><br><span class="line">| 2   | 4    |</span><br><span class="line">| 3   | NULL |</span><br><span class="line">| 1   | NULL |</span><br><span class="line">| 4   | NULL |</span><br><span class="line">+-----+------+</span><br><span class="line">4 rows in set (0.00 sec)</span><br></pre></td></tr></table></figure>
</li>
<li><p>左外连接LEFT JOIN<br>列出左边关系中所有的元组<br> 右外连接RIGHT JOIN<br>列出右边关系中所有的元组<br>SELECT * FROM Course FIRST RIGHT JOIN Course SECOND ON (FIRST.Cpno=SECOND.Cno); </p>
<p>A left join B: 是以A表的记录为基础的,A的每一条记录依次和B连接，A的记录将会全部表示出来,而B只会显示符合连接条件的记录.<br>A right join B: 是以B表的记录为基础的,B的每一条记录依次和A连接，B的记录将会全部表示出来,而A只会显示符合连接条件的记录</p>
<figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line"><span class="meta">mysql&gt;</span><span class="bash"> select First.Cno,Second.Cpno from Course First RIGHT JOIN Course Second ON(first.cpno = second.cno);</span></span><br><span class="line">+------+------+</span><br><span class="line">| Cno  | Cpno |</span><br><span class="line">+------+------+</span><br><span class="line">| 2    | 4    |</span><br><span class="line">| 3    | NULL |</span><br><span class="line">| NULL | NULL |</span><br><span class="line">| NULL | 3    |</span><br><span class="line">+------+------+</span><br><span class="line">4 rows in set (0.00 sec)</span><br></pre></td></tr></table></figure>
</li>
</ul>
<h2 id="5-复合条件连接"><a href="#5-复合条件连接" class="headerlink" title="5.复合条件连接"></a>5.复合条件连接</h2><ul>
<li><p>复合条件连接：WHERE子句中含多个连接条件<br>[例37]查询选修2号课程且成绩在90分以上的所有学生的学号和姓名        </p>
<pre><code>SELECT Student.Sno, Sname
FROM    Student, SC
WHERE Student.Sno = SC.Sno AND /* 连接谓词*/
    Cno= ‘2’ AND Grade &gt; 90；   /* 其他限定条件 */
</code></pre><figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line"><span class="meta">mysql&gt;</span><span class="bash"> select Student.sno,sname from student,sc <span class="built_in">where</span> student.Sno = sc.Sno AND Cno=<span class="string">&#x27;2&#x27;</span> AND Grade &gt;90;</span></span><br><span class="line">+-------+--------+</span><br><span class="line">| sno   | sname  |</span><br><span class="line">+-------+--------+</span><br><span class="line">| 95001 | 张三   |</span><br><span class="line">| 95005 | 苏三   |</span><br><span class="line">+-------+--------+</span><br><span class="line">2 rows in set (0.01 sec)</span><br></pre></td></tr></table></figure>
<blockquote>
<p>该查询的过程是，先从SC表中挑选出Cno=’2’并且Grade&gt;90 的元组形成一个中间关系，再和Student中满足连接条件的元组进行连接得到最终的结果关系。</p>
</blockquote>
</li>
<li><p>[例38]查询每个学生的学号、姓名、选修的课程名及成绩<br>SELECT Student.Sno，Sname，Cname，Grade<br> FROM    Student，SC，Course    /<em>多表连接</em>/<br> WHERE Student.Sno = SC.Sno </p>
<pre><code>             and SC.Cno = Course.Cno；
</code></pre><figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br></pre></td><td class="code"><pre><span class="line"><span class="meta">mysql&gt;</span><span class="bash"> select student.sno,sname,cname,grade from student,sc,course <span class="built_in">where</span> student.sno=sc.sno AND sc.cno=course.cno;</span></span><br><span class="line">+-------+-----------+-----------+-------+</span><br><span class="line">| sno   | sname     | cname     | grade |</span><br><span class="line">+-------+-----------+-----------+-------+</span><br><span class="line">| 95001 | 张三      | MATH      |    93 |</span><br><span class="line">| 95005 | 苏三      | MATH      |    90 |</span><br><span class="line">| 95011 | 张成民    | MATH      |  NULL |</span><br><span class="line">| 95001 | 张三      | DB_DESIGN |    95 |</span><br><span class="line">| 95002 | 李四      | DB_DESIGN |    90 |</span><br><span class="line">| 95003 | 王五      | DB_DESIGN |  NULL |</span><br><span class="line">| 95005 | 苏三      | DB_DESIGN |    98 |</span><br><span class="line">| 95001 | 张三      | P_DESIGN  |    91 |</span><br><span class="line">| 95002 | 李四      | P_DESIGN  |    80 |</span><br><span class="line">| 95004 | 马六      | P_DESIGN  |  NULL |</span><br><span class="line">| 95005 | 苏三      | P_DESIGN  |    90 |</span><br><span class="line">| 95001 | 张三      | OS        |    98 |</span><br><span class="line">| 95004 | 马六      | OS        |    88 |</span><br><span class="line">| 95005 | 苏三      | OS        |    89 |</span><br><span class="line">+-------+-----------+-----------+-------+</span><br></pre></td></tr></table></figure>
</li>
</ul>
<h2 id="7-嵌套查询"><a href="#7-嵌套查询" class="headerlink" title="7.嵌套查询"></a>7.嵌套查询</h2><ul>
<li><p>嵌套查询概述<br>一个SELECT-FROM-WHERE语句称为一个查询块<br>将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询</p>
</li>
<li><p>子查询的限制<br>不能使用ORDER BY子句，order by 子句只能对最终查询结果进行排序<br>层层嵌套方式反映了 SQL语言的结构化<br>有些嵌套查询可以用连接运算替代</p>
</li>
<li><p>SELECT Sname                          /<em>外层查询/父查询</em>/<br>FROM Student<br>WHERE Sno IN<br>（SELECT Sno             /<em>内层查询/子查询</em>/<br>  FROM SC<br>  WHERE Cno= ‘ 2 ‘）；   </p>
<figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line"><span class="meta">mysql&gt;</span><span class="bash"> select Sname from student <span class="built_in">where</span> sno IN (select sno from sc <span class="built_in">where</span> cno=<span class="string">&#x27;2&#x27;</span>);</span></span><br><span class="line">+--------+</span><br><span class="line">| Sname  |</span><br><span class="line">+--------+</span><br><span class="line">| 张三   |</span><br><span class="line">| 李四   |</span><br><span class="line">| 王五   |</span><br><span class="line">| 苏三   |</span><br><span class="line">+--------+</span><br></pre></td></tr></table></figure>
<blockquote>
<p>先查询选修课程号为2的学生学号，之后再在student中查询满足学号的学生姓名</p>
</blockquote>
</li>
<li><p>不相关子查询：</p>
<pre><code>查询的查询条件不依赖于父查询
</code></pre><p>由里向外逐层处理。即每个子查询在上一级查询处理之前求解，子查询的结果用于建立其父查询的查找条件。</p>
</li>
<li><p>相关子查询：子查询的查询条件依赖于父查询<br>首先取外层查询中表的第一个元组，根据它与内层查询相关的属性值处理内层查询，若WHERE子句返回值为真，则取此元组放入结果表<br>然后再取外层表的下一个元组<br>重复这一过程，直至外层表全部检查完为止</p>
</li>
</ul>
<h3 id="A-带有IN谓词的子查询"><a href="#A-带有IN谓词的子查询" class="headerlink" title="A 带有IN谓词的子查询"></a>A 带有IN谓词的子查询</h3><ul>
<li><p>[例39]  查询与“刘三姐”在同一个系学习的学生。</p>
<pre><code>此查询要求可以分步来完成
</code></pre><p>① 确定“刘三姐”所在系名             </p>
<pre><code>SELECT  Sdept  
 FROM     Student                            
 WHERE  Sname= ‘ 刘三姐 &#39;；
      结果为： IS
</code></pre><p>② 查找所有在IS系学习的学生。    </p>
<pre><code>    SELECT   Sno，Sname，Sdept     
    FROM      Student                 
    WHERE  Sdept= ‘ IS’； 
</code></pre><p>将第一步查询嵌入到第二步查询的条件中</p>
<pre><code>SELECT Sno，Sname，Sdept
    FROM Student
   WHERE Sdept  IN
              (SELECT Sdept
               FROM Student
               WHERE Sname= ‘ 刘三姐 ’)；
此查询为不相关子查询。
</code></pre><figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line"><span class="meta">mysql&gt;</span><span class="bash"> select Sno,Sname,Sdept from Student <span class="built_in">where</span> Sdept IN (select Sdept from Student <span class="built_in">where</span> Sname =<span class="string">&#x27;刘三姐&#x27;</span>);</span></span><br><span class="line">+-------+-----------+-------+</span><br><span class="line">| Sno   | Sname     | Sdept |</span><br><span class="line">+-------+-----------+-------+</span><br><span class="line">| 95002 | 李四      | IS    |</span><br><span class="line">| 95005 | 苏三      | IS    |</span><br><span class="line">| 95006 | 刘七      | IS    |</span><br><span class="line">| 95007 | 刘三姐    | IS    |</span><br><span class="line">| 95010 | 陈东      | IS    |</span><br><span class="line">+-------+-----------+-------+</span><br><span class="line">5 rows in set (0.00 sec)</span><br></pre></td></tr></table></figure>
</li>
<li><p>[例40]查询选修了课程名为“DB_DESIGN”的学生学号和姓名<br>SELECT Sno，Sname                       ③ 最后在Student关系中<br> FROM    Student                          取出Sno和Sname<br>WHERE Sno  IN</p>
<pre><code>    (SELECT Sno                         ② 然后在SC关系中找出选
     FROM    SC                           修了2号课程的学生学号
     WHERE  Cno IN
           (SELECT Cno               ① 首先在Course关系中找出
             FROM Course            “DB_DESIGN”的课程号，为2号
             WHERE Cname= ‘DB_DESIGN’
           )
     );
</code></pre><figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line"><span class="meta">mysql&gt;</span><span class="bash"> select Sno,Sname from Student <span class="built_in">where</span> Sno IN (select Sno from sc <span class="built_in">where</span> Cno IN (select Cno from Course <span class="built_in">where</span> cname=<span class="string">&#x27;DB_DES</span></span></span><br><span class="line">IGN&#x27;));</span><br><span class="line">+-------+--------+</span><br><span class="line">| Sno   | Sname  |</span><br><span class="line">+-------+--------+</span><br><span class="line">| 95001 | 张三   |</span><br><span class="line">| 95002 | 李四   |</span><br><span class="line">| 95003 | 王五   |</span><br><span class="line">| 95005 | 苏三   |</span><br><span class="line">+-------+--------+</span><br><span class="line">4 rows in set (0.00 sec)</span><br></pre></td></tr></table></figure>
</li>
</ul>
<h3 id="B-带有比较运算符的子查询"><a href="#B-带有比较运算符的子查询" class="headerlink" title="B 带有比较运算符的子查询"></a>B 带有比较运算符的子查询</h3><ul>
<li><p>当能确切知道内层查询返回单值时，可用比较运算符（&gt;，&lt;，=，&gt;=，&lt;=，!=或&lt; &gt;）。<br>与ANY或ALL谓词配合使用</p>
</li>
<li><p>［例41］找出每个学生超过他选修课程平均成绩的课程号和学号。<br>SELECT Sno， Cno<br> FROM  SC  x<br> WHERE Grade &gt;(SELECT AVG(Grade) </p>
<pre><code>           FROM  SC y
           WHERE y.Sno=x.Sno);
</code></pre><p>子查询一定要跟在比较符之后</p>
<figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line"><span class="meta">mysql&gt;</span><span class="bash"> select Sno,Cno from sc x <span class="built_in">where</span> Grade &gt;(select AVG(Grade) from sc y <span class="built_in">where</span> y.sno=x.sno);</span></span><br><span class="line">+-------+-----+</span><br><span class="line">| Sno   | Cno |</span><br><span class="line">+-------+-----+</span><br><span class="line">| 95001 | 2   |</span><br><span class="line">| 95001 | 4   |</span><br><span class="line">| 95002 | 2   |</span><br><span class="line">| 95005 | 2   |</span><br><span class="line">+-------+-----+</span><br><span class="line">4 rows in set (0.00 sec)</span><br></pre></td></tr></table></figure>
<blockquote>
<ol>
<li>从外层查询中取出SC的一个元组x，将元组x的Sno值（95001）传送给内层查询。<br>SELECT AVG(Grade)<br>FROM SC y<br>WHERE y.Sno=‘95001’;</li>
<li>执行内层查询，得到值93（近似值），用该值代替内层查询，得到外层查询：<br>SELECT Sno， Cno<br>FROM  SC x<br>WHERE Grade &gt;93； </li>
<li>执行这个查询，得到<br>（95001，2）<br>（95001，4） </li>
<li>外层查询取出下一个元组重复做上述1至3步骤，直到外层的SC元组全部处理完毕。</li>
</ol>
</blockquote>
</li>
</ul>
<h3 id="C-带有ANY（SOME）或ALL谓词的子查询"><a href="#C-带有ANY（SOME）或ALL谓词的子查询" class="headerlink" title="C.带有ANY（SOME）或ALL谓词的子查询"></a>C.带有ANY（SOME）或ALL谓词的子查询</h3><ul>
<li><p>谓词语义<br>ANY：任意一个值（只要有一个值）<br>ALL：所有值</p>
</li>
<li><p>需要配合使用比较运算符</p>
<blockquote>
<p>>ANY    大于子查询结果中的某个值<br>>ALL    大于子查询结果中的所有值<br>&lt; ANY    小于子查询结果中的某个值<br>&lt; ALL    小于子查询结果中的所有值<br>>= ANY    大于等于子查询结果中的某个值<br>>= ALL    大于等于子查询结果中的所有值<br>&lt;= ANY    小于等于子查询结果中的某个值<br>&lt;= ALL    小于等于子查询结果中的所有值<br>= ANY    等于子查询结果中的某个值<br>=ALL    等于子查询结果中的所有值（通常没有实际意义）<br>!=（或&lt;&gt;）ANY    不等于子查询结果中的某个值<br>!=（或&lt;&gt;）ALL    不等于子查询结果中的任何一个值</p>
</blockquote>
</li>
<li><p>[例42]  查询其他系中比计算机科学某一学生年龄小的学生姓名和年龄</p>
<pre><code>SELECT Sname，Sage
FROM    Student
WHERE Sage &lt; ANY (SELECT  Sage
                  FROM    Student
                  WHERE Sdept= &#39; CS &#39;)
       AND Sdept &lt;&gt; ‘CS &#39; ;           /*父查询块中的条件 */
</code></pre><figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line"><span class="meta">mysql&gt;</span><span class="bash"> select Sname,Sage from student <span class="built_in">where</span> Sage &lt; ANY (select Sage from student <span class="built_in">where</span> Sdept =<span class="string">&#x27;CS&#x27;</span>) AND Sdept &lt;&gt;<span class="string">&#x27;CS&#x27;</span>;</span></span><br><span class="line">+--------+------+</span><br><span class="line">| Sname  | Sage |</span><br><span class="line">+--------+------+</span><br><span class="line">| 王五   |   19 |</span><br><span class="line">| 苏三   |   20 |</span><br><span class="line">| 刘七   |   19 |</span><br><span class="line">| 陈东   |   19 |</span><br><span class="line">+--------+------+</span><br><span class="line">4 rows in set (0.00 sec)</span><br></pre></td></tr></table></figure>
<blockquote>
<p>执行过程：<br>1.RDBMS执行此查询时，首先处理子查询，找出<br>   CS系中所有学生的年龄，构成一个集合(21，19)</p>
<ol>
<li>处理父查询，找所有不是CS系且年龄小于<br>21 或 19的学生</li>
</ol>
</blockquote>
</li>
<li><p>用聚集函数实现[例42] </p>
<p>SELECT Sname，Sage<br> FROM   Student<br> WHERE Sage &lt; (SELECT MAX(Sage)</p>
<pre><code>           FROM Student
           WHERE Sdept= ‘CS &#39;)
   AND Sdept &lt;&gt; &#39; CS &#39;;
</code></pre><figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line"><span class="meta">mysql&gt;</span><span class="bash"> select Sname,Sage from student <span class="built_in">where</span> Sage &lt;(select MAX(Sage) from student <span class="built_in">where</span> Sdept =<span class="string">&#x27;CS&#x27;</span>) AND Sdept &lt;&gt;<span class="string">&#x27;CS&#x27;</span>;</span></span><br><span class="line">+--------+------+</span><br><span class="line">| Sname  | Sage |</span><br><span class="line">+--------+------+</span><br><span class="line">| 王五   |   19 |</span><br><span class="line">| 苏三   |   20 |</span><br><span class="line">| 刘七   |   19 |</span><br><span class="line">| 陈东   |   19 |</span><br><span class="line">+--------+------+</span><br><span class="line">4 rows in set (0.00 sec)</span><br></pre></td></tr></table></figure>
</li>
<li><p>[例43]  查询其他系中比计算机科学系所有学生年龄都小的学生姓名及年龄。</p>
<p>方法一：用ALL谓词</p>
<pre><code>SELECT Sname，Sage
FROM Student
WHERE Sage &lt; ALL(SELECT Sage
                 FROM Student
                 WHERE Sdept= &#39; CS &#39;)
    AND Sdept &lt;&gt; &#39; CS &#39;;
</code></pre><p>方法二：用聚集函数</p>
<pre><code>SELECT Sname，Sage
FROM Student
WHERE Sage &lt;(SELECT MIN(Sage)
             FROM Student
             WHERE Sdept= &#39; CS &#39;)
      AND Sdept &lt;&gt;&#39; CS ’;
</code></pre></li>
</ul>
<h3 id="D-带有EXISTS谓词的子查询"><a href="#D-带有EXISTS谓词的子查询" class="headerlink" title="D 带有EXISTS谓词的子查询"></a>D 带有EXISTS谓词的子查询</h3><ul>
<li><p>EXISTS谓词<br>存在量词<br>带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。<br>若内层查询结果非空，则外层的WHERE子句返回真值<br>若内层查询结果为空，则外层的WHERE子句返回假值<br>由EXISTS引出的子查询，其目标列表达式通常都用* ，因为带EXISTS的子查询只返回真值或假值，给出列名无实际意义</p>
</li>
<li><p>NOT EXISTS谓词<br>若内层查询结果非空，则外层的WHERE子句返回假值<br>若内层查询结果为空，则外层的WHERE子句返回真值</p>
</li>
<li><p>[例44]查询所有选修了1号课程的学生姓名。</p>
<p>思路分析：<br>本查询涉及Student和SC关系<br>在Student中依次取每个元组的Sno值，用此值去检查SC关系<br>若SC中存在这样的元组，其Sno值等于此Student.Sno值，并且其Cno= ‘1’，则取此Student.Sname送入结果关系</p>
<p>用嵌套查询</p>
<pre><code> SELECT Sname
 FROM Student
 WHERE EXISTS
             (SELECT *
              FROM SC
              WHERE Sno=Student.Sno AND Cno= &#39; 1 &#39;)；
</code></pre><figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line"><span class="meta">mysql&gt;</span><span class="bash"> select Sname from Student <span class="built_in">where</span> EXISTS(select * from sc <span class="built_in">where</span> Sno=student.sno AND Cno=<span class="string">&#x27;1&#x27;</span>);</span></span><br><span class="line">+-----------+</span><br><span class="line">| Sname     |</span><br><span class="line">+-----------+</span><br><span class="line">| 张三      |</span><br><span class="line">| 苏三      |</span><br><span class="line">| 张成民    |</span><br><span class="line">+-----------+</span><br><span class="line">3 rows in set (0.00 sec)</span><br></pre></td></tr></table></figure>
<p>用连接做</p>
<p>SELECT Sname<br>FROM Student, SC<br>WHERE Student.Sno=SC.Sno AND SC.Cno= ‘1’;</p>
</li>
<li><p>[例45]  查询没有选修1号课程的学生姓名。</p>
<pre><code>SELECT Sname
FROM Student
WHERE NOT EXISTS
              (SELECT *
               FROM SC
               WHERE Sno = Student.Sno AND Cno=&#39;1&#39;)；
</code></pre><figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br></pre></td><td class="code"><pre><span class="line"><span class="meta">mysql&gt;</span><span class="bash"> select Sname from student <span class="built_in">where</span> NOT EXISTS(select * from sc <span class="built_in">where</span> sno=student.sno AND Cno=<span class="string">&#x27;1&#x27;</span>);</span></span><br><span class="line">+--------------+</span><br><span class="line">| Sname        |</span><br><span class="line">+--------------+</span><br><span class="line">| 李四         |</span><br><span class="line">| 王五         |</span><br><span class="line">| 马六         |</span><br><span class="line">| 刘七         |</span><br><span class="line">| 刘三姐       |</span><br><span class="line">| 欧阳锋       |</span><br><span class="line">| 欧阳大侠     |</span><br><span class="line">| 陈东         |</span><br><span class="line">+--------------+</span><br></pre></td></tr></table></figure>
</li>
</ul>
<ul>
<li>不同形式的查询间的替换<br>一些带EXISTS或NOT EXISTS谓词的子查询不能被其他形式的子查询等价替换<br>所有带IN谓词、比较运算符、ANY和ALL谓词的子查询都能用带EXISTS谓词的子查询等价替换<br> 用EXISTS/NOT EXISTS实现全称量词(难点)<br>SQL语言中没有全称量词 （For all）<br>可以把带有全称量词的谓词转换为等价的带有存在量词的谓词：</li>
</ul>
<ul>
<li><p>例：[例39]查询与“刘三姐”在同一个系学习的学生。</p>
<pre><code>可以用带EXISTS谓词的子查询替换：
</code></pre><p>  SELECT Sno，Sname，Sdept<br>  FROM Student S1<br>   WHERE EXISTS</p>
<pre><code>      　   (SELECT *
              FROM Student S2
              WHERE S2.Sdept = S1.Sdept AND
                            S2.Sname = ‘刘三姐’)；
</code></pre><figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br></pre></td><td class="code"><pre><span class="line"><span class="meta">mysql&gt;</span><span class="bash"> select Sno,Sname,Sdept from Student S1 <span class="built_in">where</span> exists (select * from student S2 <span class="built_in">where</span></span></span><br><span class="line"> S1.sdept=S2.sdept AND S2.Sname=&#x27;刘三姐&#x27;);</span><br><span class="line">+-------+-----------+-------+</span><br><span class="line">| Sno   | Sname     | Sdept |</span><br><span class="line">+-------+-----------+-------+</span><br><span class="line">| 95002 | 李四      | IS    |</span><br><span class="line">| 95005 | 苏三      | IS    |</span><br><span class="line">| 95006 | 刘七      | IS    |</span><br><span class="line">| 95007 | 刘三姐    | IS    |</span><br><span class="line">| 95010 | 陈东      | IS    |</span><br><span class="line">+-------+-----------+-------+</span><br><span class="line">6 rows in set (2.33 sec)</span><br></pre></td></tr></table></figure>
</li>
<li><p>[例46] 查询选修了全部课程的学生姓名。<br>查询这样的学生，没有一门课是他不选的。 </p>
<pre><code> SELECT Sname
 FROM Student
 WHERE NOT EXISTS     -----不存在 
    （SELECT *  FROM Course   ----有这样一条记录（有这样一门课）
</code></pre><p>—-这个学生没有选这门课（不存在这样一门课，这个学生选了的）  </p>
<pre><code>          WHERE NOT EXISTS           ----不存在   
                    (SELECT *
                     FROM SC
                     WHERE Sno= Student.Sno  ---这个学生 
                     AND Cno= Course.Cno   ---这门课 
                   ）
             ）；
</code></pre></li>
</ul>
<figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line"><span class="meta">mysql&gt;</span><span class="bash"> select Sname from student <span class="built_in">where</span> NOT EXISTS(select * from COurse <span class="built_in">where</span> NOT EXISTS (select * from sc <span class="built_in">where</span> Sno=student.sno AND CNO=course.Cno));</span></span><br><span class="line">+--------+</span><br><span class="line">| Sname  |</span><br><span class="line">+--------+</span><br><span class="line">| 张三   |</span><br><span class="line">| 苏三   |</span><br><span class="line">+--------+</span><br><span class="line">2 rows in set (0.01 sec)</span><br><span class="line"></span><br></pre></td></tr></table></figure>
<ul>
<li>[例47]查询至少选修了学生95002选修的全部课程的学生号码。（除运算）<br>解题思路：<br>用逻辑蕴函表达：查询学号为x的学生，对所有的课程y，只要95002学生选修了课程y，则x也选修了y。<br>形式化表示：<pre><code>用p表示谓词 “学生95002选修了课程y”
用q表示谓词 “学生x选修了课程y”
</code></pre></li>
<li>变换后语义：不存在这样的课程y，学生95002选修了y，而学生x没有选。</li>
</ul>
<p>用NOT EXISTS谓词表示：<br>     SELECT DISTINCT Sno<br>       FROM SC SCX<br>       WHERE NOT EXISTS ——-不存在<br>                     (SELECT <em><br>                      FROM SC SCY ——有95002选的一门课<br>                      WHERE SCY.Sno = ‘ 95002 ‘  AND<br>                                  —-这个学生没有选95002选的这门课<br>                                    NOT EXISTS ——-不存在<br>                                    (SELECT </em><br>                                     FROM SC SCZ ——有这样一条选课记录<br>                                     WHERE SCZ.Sno=SCX.Sno —-这个学生<br>                                        AND  SCZ.Cno=SCY.Cno))；—-这门课 </p>
<blockquote>
<p>首先选出x的第一行，和y的95002的数据的第一行，来判断第三张表z中的课程是否和y的第一行课程一样，如果一样则表明存在，NOT EXISTS为假，继续判断95002的第二行在第三张表中是否存在，如果不存在，那就为真，上面的NOT EXISTS就为假，这个学号不输出</p>
</blockquote>
<figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line"><span class="meta">mysql&gt;</span><span class="bash"> select DISTINCT SNO from SC scx <span class="built_in">where</span> NOT EXISTS(select * from sc scy <span class="built_in">where</span> scy.Sno</span></span><br><span class="line">=&#x27;95002&#x27; AND NOT EXISTS(select * from sc scz where scz.Sno=scx.sno AND scz.cno=scy.cno));</span><br><span class="line">+-------+</span><br><span class="line">| SNO   |</span><br><span class="line">+-------+</span><br><span class="line">| 95001 |</span><br><span class="line">| 95002 |</span><br><span class="line">| 95005 |</span><br><span class="line">+-------+</span><br><span class="line">3 rows in set (0.01 sec)</span><br><span class="line"></span><br></pre></td></tr></table></figure>
<h2 id="8-集合查询"><a href="#8-集合查询" class="headerlink" title="8.集合查询"></a>8.集合查询</h2><ul>
<li><p>集合操作的种类<br>并操作UNION<br>交集<br>差<br>笛卡尔积<br>参加集合操作的各查询结果的列数必须相同；对应项的数据类型也必须相同 </p>
</li>
<li><p>[例48]  查询计算机科学系的学生及年龄不大于19岁的学生。<br>方法一：</p>
<pre><code>    SELECT *
    FROM Student
    WHERE Sdept= &#39;CS&#39;
    UNION
    SELECT *
    FROM Student
    WHERE Sage&lt;=19；
</code></pre><p>UNION：将多个查询结果合并起来时，系统自动去掉重复元组。<br>UNION ALL：将多个查询结果合并起来时，保留重复元组 </p>
<figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br></pre></td><td class="code"><pre><span class="line"><span class="meta">mysql&gt;</span><span class="bash"> select * from student <span class="built_in">where</span> sdept =<span class="string">&#x27;CS&#x27;</span> UNION select * from student <span class="built_in">where</span> Sage&lt;=19;</span></span><br><span class="line"></span><br><span class="line">+-------+-----------+------+------+-------+</span><br><span class="line">| Sno   | Sname     | Ssex | Sage | Sdept |</span><br><span class="line">+-------+-----------+------+------+-------+</span><br><span class="line">| 95001 | 张三      | 男   |   21 | CS    |</span><br><span class="line">| 95004 | 马六      | 女   |   20 | CS    |</span><br><span class="line">| 95011 | 张成民    | 男   |   19 | CS    |</span><br><span class="line">| 95003 | 王五      | 男   |   19 | MA    |</span><br><span class="line">| 95006 | 刘七      | 女   |   19 | IS    |</span><br><span class="line">| 95010 | 陈东      | 男   |   19 | IS    |</span><br><span class="line">+-------+-----------+------+------+-------+</span><br><span class="line">6 rows in set (0.00 sec)</span><br><span class="line"></span><br></pre></td></tr></table></figure>
</li>
<li><p>方法二：用多重条件查询</p>
<p>SELECT  DISTINCT  *<br>  FROM Student<br>  WHERE Sdept= ‘CS’  OR  Sage&lt;=19；</p>
<figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br></pre></td><td class="code"><pre><span class="line"><span class="meta">mysql&gt;</span><span class="bash"> select DISTINCT * from Student <span class="built_in">where</span> sdept =<span class="string">&#x27;CS&#x27;</span> OR Sage&lt;=19;</span></span><br><span class="line">+-------+-----------+------+------+-------+</span><br><span class="line">| Sno   | Sname     | Ssex | Sage | Sdept |</span><br><span class="line">+-------+-----------+------+------+-------+</span><br><span class="line">| 95001 | 张三      | 男   |   21 | CS    |</span><br><span class="line">| 95003 | 王五      | 男   |   19 | MA    |</span><br><span class="line">| 95004 | 马六      | 女   |   20 | CS    |</span><br><span class="line">| 95006 | 刘七      | 女   |   19 | IS    |</span><br><span class="line">| 95010 | 陈东      | 男   |   19 | IS    |</span><br><span class="line">| 95011 | 张成民    | 男   |   19 | CS    |</span><br><span class="line">+-------+-----------+------+------+-------+</span><br><span class="line">6 rows in set (0.00 sec)</span><br><span class="line"></span><br></pre></td></tr></table></figure>
</li>
<li><p>[例49]  查询选修了课程1或者选修了课程2的学生。</p>
<p>SELECT Sno<br>FROM SC<br>WHERE Cno=’ 1 ‘<br>UNION<br>SELECT Sno<br>FROM SC<br>WHERE Cno= ‘ 2 ‘；</p>
<figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br></pre></td><td class="code"><pre><span class="line"><span class="meta">mysql&gt;</span><span class="bash"> select Sno from SC <span class="built_in">where</span> Cno=<span class="string">&#x27;1&#x27;</span> UNION select Sno from SC <span class="built_in">where</span> Cno=<span class="string">&#x27;2&#x27;</span>;</span></span><br><span class="line">+-------+</span><br><span class="line">| Sno   |</span><br><span class="line">+-------+</span><br><span class="line">| 95001 |</span><br><span class="line">| 95005 |</span><br><span class="line">| 95011 |</span><br><span class="line">| 95002 |</span><br><span class="line">| 95003 |</span><br><span class="line">+-------+</span><br><span class="line">5 rows in set (0.00 sec)</span><br><span class="line"></span><br></pre></td></tr></table></figure>
</li>
<li><p>[例50]  查询计算机科学系的学生与年龄不大于19岁的学生的交集</p>
<p>SELECT * FROM Student WHERE Sdept=’CS’ AND Sage&lt;=19;</p>
<figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line"><span class="meta">mysql&gt;</span><span class="bash"> select * from student <span class="built_in">where</span> sdept = <span class="string">&#x27;CS&#x27;</span> AND Sage&lt;=19;</span></span><br><span class="line">+-------+-----------+------+------+-------+</span><br><span class="line">| Sno   | Sname     | Ssex | Sage | Sdept |</span><br><span class="line">+-------+-----------+------+------+-------+</span><br><span class="line">| 95011 | 张成民    | 男     |   19 | CS    |</span><br><span class="line">+-------+-----------+------+------+-------+</span><br><span class="line">1 row in set (0.00 sec)</span><br><span class="line"></span><br></pre></td></tr></table></figure>
<blockquote>
<p>这里实际上要用到交集INTERSECT，但是和前面多个条件查询一样所以就没有用INTERSECT关键词。</p>
</blockquote>
</li>
<li><p>[例51]  查询选修课程1的学生集合与选修课程2的学生集合的交集</p>
<p>SELECT Sno FROM SC WHERE Cno=1<br>AND Sno IN (SELECT Sno FROM SC<br>WHERE Cno=2);</p>
<figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line"><span class="meta">mysql&gt;</span><span class="bash"> select Sno from sc <span class="built_in">where</span> cno=<span class="string">&#x27;1&#x27;</span> and sno IN(select sno from sc <span class="built_in">where</span> cno=<span class="string">&#x27;2&#x27;</span>);</span></span><br><span class="line">+-------+</span><br><span class="line">| Sno   |</span><br><span class="line">+-------+</span><br><span class="line">| 95001 |</span><br><span class="line">| 95005 |</span><br><span class="line">+-------+</span><br><span class="line">2 rows in set (0.00 sec)</span><br><span class="line"></span><br></pre></td></tr></table></figure>
</li>
<li><p>[例52]  查询计算机科学系的学生与年龄不大于19岁的学生的差集。</p>
<p>SELECT * FROM Student WHERE Sdept=’CS’ AND Sage&gt;19;</p>
<figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line"><span class="meta">mysql&gt;</span><span class="bash"> select * from student <span class="built_in">where</span> sdept =<span class="string">&#x27;CS&#x27;</span> AND sage&gt;19;</span></span><br><span class="line">+-------+--------+------+------+-------+</span><br><span class="line">| Sno   | Sname  | Ssex | Sage | Sdept |</span><br><span class="line">+-------+--------+------+------+-------+</span><br><span class="line">| 95001 | 张三   | 男   |   21 | CS    |</span><br><span class="line">| 95004 | 马六   | 女   |   20 | CS    |</span><br><span class="line">+-------+--------+------+------+-------+</span><br><span class="line">2 rows in set (0.00 sec)</span><br><span class="line"></span><br></pre></td></tr></table></figure>
</li>
<li><p>[例52]  查询学生表与课程表的笛卡尔积。</p>
<p>SELECT * FROM Student CROSS JOIN Course;</p>
<figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br></pre></td><td class="code"><pre><span class="line"><span class="meta">mysql&gt;</span><span class="bash"> select * from student CROSS JOIN course;</span></span><br><span class="line">+-------+--------------+------+------+-------+-----+-----------+------+---------+</span><br><span class="line">| Sno   | Sname        | Ssex | Sage | Sdept | Cno | Cname     | Cpno | Ccredit |</span><br><span class="line">+-------+--------------+------+------+-------+-----+-----------+------+---------+</span><br><span class="line">| 95001 | 张三         | 男   |   21 | CS    | 1   | MATH      | NULL |       6 |</span><br><span class="line">| 95001 | 张三         | 男   |   21 | CS    | 2   | DB_DESIGN | 3    |       2 |</span><br><span class="line">| 95001 | 张三         | 男   |   21 | CS    | 3   | P_DESIGN  | 4    |       3 |</span><br><span class="line">| 95001 | 张三         | 男   |   21 | CS    | 4   | OS        | NULL |       2 |</span><br><span class="line">| 95002 | 李四         | 男   |   23 | IS    | 1   | MATH      | NULL |       6 |</span><br><span class="line">| 95002 | 李四         | 男   |   23 | IS    | 2   | DB_DESIGN | 3    |       2 |</span><br><span class="line">| 95002 | 李四         | 男   |   23 | IS    | 3   | P_DESIGN  | 4    |       3 |</span><br><span class="line">| 95002 | 李四         | 男   |   23 | IS    | 4   | OS        | NULL |       2 |</span><br><span class="line">| 95003 | 王五         | 男   |   19 | MA    | 1   | MATH      | NULL |       6 |</span><br><span class="line">| 95003 | 王五         | 男   |   19 | MA    | 2   | DB_DESIGN | 3    |       2 |</span><br><span class="line">| 95003 | 王五         | 男   |   19 | MA    | 3   | P_DESIGN  | 4    |       3 |</span><br><span class="line">| 95003 | 王五         | 男   |   19 | MA    | 4   | OS        | NULL |       2 |</span><br><span class="line">| 95004 | 马六         | 女   |   20 | CS    | 1   | MATH      | NULL |       6 |</span><br><span class="line">| 95004 | 马六         | 女   |   20 | CS    | 2   | DB_DESIGN | 3    |       2 |</span><br><span class="line">| 95004 | 马六         | 女   |   20 | CS    | 3   | P_DESIGN  | 4    |       3 |</span><br><span class="line">| 95004 | 马六         | 女   |   20 | CS    | 4   | OS        | NULL |       2 |</span><br><span class="line">| 95005 | 苏三         | 女   |   20 | IS    | 1   | MATH      | NULL |       6 |</span><br><span class="line">| 95005 | 苏三         | 女   |   20 | IS    | 2   | DB_DESIGN | 3    |       2 |</span><br><span class="line">| 95005 | 苏三         | 女   |   20 | IS    | 3   | P_DESIGN  | 4    |       3 |</span><br><span class="line">| 95005 | 苏三         | 女   |   20 | IS    | 4   | OS        | NULL |       2 |</span><br><span class="line">| 95006 | 刘七         | 女   |   19 | IS    | 1   | MATH      | NULL |       6 |</span><br><span class="line">| 95006 | 刘七         | 女   |   19 | IS    | 2   | DB_DESIGN | 3    |       2 |</span><br><span class="line">| 95006 | 刘七         | 女   |   19 | IS    | 3   | P_DESIGN  | 4    |       3 |</span><br><span class="line">| 95006 | 刘七         | 女   |   19 | IS    | 4   | OS        | NULL |       2 |</span><br><span class="line">| 95007 | 刘三姐       | 女   |   23 | IS    | 1   | MATH      | NULL |       6 |</span><br><span class="line">| 95007 | 刘三姐       | 女   |   23 | IS    | 2   | DB_DESIGN | 3    |       2 |</span><br><span class="line">| 95007 | 刘三姐       | 女   |   23 | IS    | 3   | P_DESIGN  | 4    |       3 |</span><br><span class="line">| 95007 | 刘三姐       | 女   |   23 | IS    | 4   | OS        | NULL |       2 |</span><br><span class="line">| 95008 | 欧阳锋       | 男   |   24 | MA    | 1   | MATH      | NULL |       6 |</span><br><span class="line">| 95008 | 欧阳锋       | 男   |   24 | MA    | 2   | DB_DESIGN | 3    |       2 |</span><br><span class="line">| 95008 | 欧阳锋       | 男   |   24 | MA    | 3   | P_DESIGN  | 4    |       3 |</span><br><span class="line">| 95008 | 欧阳锋       | 男   |   24 | MA    | 4   | OS        | NULL |       2 |</span><br><span class="line">| 95009 | 欧阳大侠     | 男   |   23 | MA    | 1   | MATH      | NULL |       6 |</span><br><span class="line">| 95009 | 欧阳大侠     | 男   |   23 | MA    | 2   | DB_DESIGN | 3    |       2 |</span><br><span class="line">| 95009 | 欧阳大侠     | 男   |   23 | MA    | 3   | P_DESIGN  | 4    |       3 |</span><br><span class="line">| 95009 | 欧阳大侠     | 男   |   23 | MA    | 4   | OS        | NULL |       2 |</span><br><span class="line">| 95010 | 陈东         | 男   |   19 | IS    | 1   | MATH      | NULL |       6 |</span><br><span class="line">| 95010 | 陈东         | 男   |   19 | IS    | 2   | DB_DESIGN | 3    |       2 |</span><br><span class="line">| 95010 | 陈东         | 男   |   19 | IS    | 3   | P_DESIGN  | 4    |       3 |</span><br><span class="line">| 95010 | 陈东         | 男   |   19 | IS    | 4   | OS        | NULL |       2 |</span><br><span class="line">| 95011 | 张成民       | 男   |   19 | CS    | 1   | MATH      | NULL |       6 |</span><br><span class="line">| 95011 | 张成民       | 男   |   19 | CS    | 2   | DB_DESIGN | 3    |       2 |</span><br><span class="line">| 95011 | 张成民       | 男   |   19 | CS    | 3   | P_DESIGN  | 4    |       3 |</span><br><span class="line">| 95011 | 张成民       | 男   |   19 | CS    | 4   | OS        | NULL |       2 |</span><br><span class="line">+-------+--------------+------+------+-------+-----+-----------+------+---------+</span><br><span class="line">48 rows in set (0.00 sec)</span><br><span class="line"></span><br></pre></td></tr></table></figure>
</li>
<li><p>插入-子查询</p>
<p>更新-子查询</p>
<p>删除-子查询</p>
<ul>
<li><p>INTO子句(与插入元组类似)</p>
<p>子查询<br>SELECT子句目标列必须与INTO子句匹配<br>值的个数<br>值的类型</p>
</li>
</ul>
</li>
<li><p>[例53]  对每一个系，求学生的平均年龄，并把结果存入数据库。</p>
<p>第一步：建表</p>
<pre><code>  CREATE  TABLE  Dept_age
      (Sdept  CHAR(15)               /* 系名*/
       Avg_age SMALLINT)；       /*学生平均年龄*/
</code></pre><p>第二步：插入数据</p>
<pre><code>    INSERT
     INTO  Dept_age(Sdept，Avg_age)
          SELECT  Sdept，AVG(Sage)
          FROM  Student
          GROUP BY Sdept；                                      
</code></pre></li>
<li><p>[例54]  将计算机科学系全体学生的成绩加1。</p>
<pre><code>UPDATE SC
SET  Grade=Grade+1
WHERE  &#39;CS&#39;=
               (SELECT Sdept
                FROM  Student
                WHERE  Student.Sno = SC.Sno);
</code></pre></li>
<li><p>[例55]  删除计算机科学系中没有成绩的学生的选课记录。</p>
<p>DELETE FROM SC WHERE ‘CS’=(SELECT Sdept FROM Student WHERE Student.Sno=SC.Sno)<br>and grade is null;</p>
</li>
</ul>
<h2 id="9-结尾"><a href="#9-结尾" class="headerlink" title="9.结尾"></a>9.结尾</h2><ul>
<li>本篇文章主要讲了多表查询的操作，其中EXSIST语句最不好搞，作者也没想到好的描述方法，如果有好的方法可以评论共同探讨，如果觉得有帮助的话，请点赞鼓励我更上一层楼！</li>
</ul>

        </div>

        
            <section class="post-copyright">
                
                    <p class="copyright-item">
                        <span>Author:</span>
                        <span><a href="/about/">WD</a></span>
                    </p>
                
                
                    <p class="copyright-item">
                        <span>Permalink:</span>
                        <span><a href="https://did321.gitee.io/2019/06/17/Mysql%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0-%E5%9B%9B-%E2%80%94%E2%80%94%E5%A4%9A%E8%A1%A8%E6%9F%A5%E8%AF%A2/">https://did321.gitee.io/2019/06/17/Mysql%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0-%E5%9B%9B-%E2%80%94%E2%80%94%E5%A4%9A%E8%A1%A8%E6%9F%A5%E8%AF%A2/</a></span>
                    </p>
                
                
                    <p class="copyright-item">
                        <span>License:</span>
                        <span>Copyright (c) 2022 <a target="_blank" rel="noopener" href="http://creativecommons.org/licenses/by-nc/4.0/">CC-BY-NC-4.0</a> LICENSE</span>
                    </p>
                
                
                     <p class="copyright-item">
                         <span>Slogan:</span>
                         <span><a href="#">The blog is my giant.</a></span>
                     </p>
                

            </section>
        
        <section class="post-tags">
            <div>
                <span>Tag(s):</span>
                <span class="tag">
                    
                    
                        <a href="/tags/Mysql/"># Mysql</a>
                    
                        <a href="/tags/%E6%95%B0%E6%8D%AE%E5%BA%93/"># 数据库</a>
                    
                        
                </span>
            </div>
            <div>
                <a href="javascript:window.history.back();">back</a>
                <span>· </span>
                <a href="/">home</a>
            </div>
        </section>
        <section class="post-nav">
            
                <a class="prev" rel="prev" href="/2019/07/10/Machine-Learning-%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0-%E4%B8%80-%E2%80%94%E2%80%94%E6%A6%82%E5%BF%B5%E5%92%8C%E4%BB%A3%E4%BB%B7%E5%87%BD%E6%95%B0/">Machine Learning 学习笔记(一)——概念和代价函数</a>
            
            
            <a class="next" rel="next" href="/2019/06/15/Mysql%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0-%E4%B8%89-%E2%80%94%E2%80%94%E5%8D%95%E8%A1%A8%E6%9F%A5%E8%AF%A2/">Mysql学习笔记(三)——单表查询</a>
            
        </section>
        <br>
        <br>
    
    <script src="//unpkg.com/valine/dist/Valine.min.js"></script>
    <div id="vcomments"></div>
    <script>
        new Valine({
    el: '#vcomments' ,

    appId: 'JvFy3ebVLo2rUYgHaMweJyXX-MdYXbMMI',
    appKey: 'TCFxfjDAM8UmERPEgYXJmT40',
    serverURLs: 'https://JvFy3ebV.api.lncldglobal.com', 
    placeholder: '----评论区----留下你的评论，作者会定期回复！在昵称处填写QQ号可自动获取邮箱和QQ头像（保护QQ邮箱隐私）',
    enableQQ: true,
    requiredFields: ['nick'],
});
    </script>

    </article>
</div>

        </div>
        <footer id="footer" class="footer">
    <div class="copyright">
        <span>© WD | Powered by <a href="https://hexo.io" target="_blank">Hexo</a> & <a href="https://github.com/Siricee/hexo-theme-Chic" target="_blank">Chic</a></span>
    </div>
</footer>

    </div>
</body>

</html>
